const express = require('express');
const cors = require('cors');
const bodyParser = require('body-parser');//用来解析请求体的 还有app.use(bodyParser.json())是一个组合
const jwt = require('jsonwebtoken');
const passport = require('passport')
const app = express();
app.use(cors())

//登录验证
const conn = require('./db')
app.use(bodyParser.json());
const keys = require('../config/keys')
app.post('/login', (req, res) => {
    const { code, password, identity } = req.body;
    let sql = "SELECT * FROM login_info WHERE code = ? AND password = ? AND identity = ? "
    conn.query(sql, [code, password, identity], (error, results) => {
        if (error) {
            res.status(500)
        } else if (results.length > 0) {
            const rule = {
                code: results[0].code,
                password: results[0].password,
                identity: results[0].identity,
                grade: results[0].grade
            }
            jwt.sign(rule, keys.secretOrkey, { expiresIn: 1800 }, (error, token) => {
                if (error) {
                    throw error
                } else {
                    res.json({
                        msg: '登录成功',
                        success: true,
                        token: "Bearer " + token,//必需是"Bearer"+token
                        code: results[0].code,
                        password: results[0].password,
                        grade: results[0].grade,
                        identity: results[0].identity,
                    })

                }

            })
        } else {
            res.json({
                msg: '用户名,身份或者密码错误',
                success: false
            })
        }
    })
})



//把passport传过去就可以分开写了,不用在那边在导入了(也把那边写的导入过来了)
require('../config/passport')(passport)
//引入后初始化和设置一个存储,才能正常使用
app.use(passport.initialize());


// 主页面
app.get('/home', passport.authenticate('jwt', { session: false }), (req, res) => {
    let sql = 'SELECT * FROM person_info WHERE code =' + req.user.code
    conn.query(sql, (error, results) => {
        if (error) res.status(500)
        if (results) {
            res.json(results[0])
        } else {
            res.json('获取失败')
        }
    })
})


//添加学生信息
app.post('/addStudent', passport.authenticate('jwt', { session: false }), (req, res) => {
    let sql = 'INSERT INTO person_info(code,name,identity,age,grade,del,address,sex) VALUES(?,?,?,?,?,?,?,?)';
    const { code, name, identity, age, grade, del, address, sex } = req.body;
    conn.query(sql, [code, name, identity, age, grade, del, address, sex], (error, results) => {
        if (error) res.status(500).json({ msg: "添加失败" })
        let sql1 = 'INSERT INTO grade_query(code,grade,name,identity) VALUES(?,?,?,?)';
        const { code, grade, name, identity } = req.body;
        conn.query(sql1, [code, grade, name, identity], (error1, results1) => {
            if (error1) res.status(500).json({ msg: "添加失败" })
            let sql2 = 'INSERT INTO login_info(code,password,identity,name,grade) VALUES(?,?,?,?,?)';
            const { code, identity, name, grade } = req.body;
            conn.query(sql2, [code, code, identity, name, grade], (error2, results2) => {
                if (error2) res.status(500).json({ msg: "添加失败" })
                res.json({ msg: "添加成功" })
            })
        })
    })
})

//添加学生成绩
app.post('/addStudentGrade', passport.authenticate('jwt', { session: false }), (req, res) => {
    let sql = 'INSERT INTO grade_query(code,grade,name,math,Python,Web,Java,dataVisual,identity) VALUES(?,?,?,?,?,?,?,?,?)';
    const { code, grade, name, math, Python, Web, Java, dataVisual, identity } = req.body;
    conn.query(sql, [code, grade, name, math, Python, Web, Java, dataVisual, identity], (error, results) => {
        if (error) res.status(500).json({ msg: "添加失败" })
        let sql1 = 'INSERT INTO login_info(code,password,identity,name,grade) VALUES(?,?,?,?,?) ';
        const { code, identity, name, grade } = req.body;
        conn.query(sql1, [code, code, identity, name, grade], (error1, results1) => {
            if (error1) res.status(500).json({ msg: "添加失败" })
            let sql2 = 'INSERT INTO person_info(code,name,identity,grade) VALUES(?,?,?,?)';
            const { code, name, identity, grade } = req.body;
            conn.query(sql2, [code, name, identity, grade], (error2, results2) => {
                if (error2) res.status(500).json({ msg: "添加失败" })
                res.json({ msg: "添加成功" })
            })
        })
    })
})


//教师获取全部学生信息(有参数，带条件搜索)
app.get('/getAllStudentInfo/', passport.authenticate('jwt', { session: false }), (req, res) => {
    const params = req.query
    const queryParams = params.queryParams
    const queryParamsValue = `%${queryParams}%`
    let sql = `SELECT * FROM person_info WHERE identity = '学生' AND (name LIKE ? OR code LIKE ? OR grade LIKE ?)`
    conn.query(sql, [queryParamsValue, queryParamsValue, queryParamsValue], (error, results) => {
        if (error) res.status(500)
        if (results.length > 0) {
            res.json(results)
        } else {
            res.json('获取失败')
        }
    })
})

//教师获取全部学生信息(无参数)
app.get('/getAllStudentInfo1/', passport.authenticate('jwt', { session: false }), (req, res) => {
    let sql = `SELECT * FROM person_info WHERE identity = '学生' `
    conn.query(sql, (error, results) => {
        if (error) res.status(500)
        if (results.length > 0) {
            res.json(results)
        } else {
            res.json('获取失败')
        }
    })
})

//修改学生个人信息(根据code学号,学生自己修改)教师也可以修改个人信息
app.post('/upDateStudentInfo', passport.authenticate('jwt', { session: false }), async (req, res) => {
    let sql = 'UPDATE person_info SET name = ?, identity = ?, age = ?, grade = ?, del = ?, address = ?, sex = ? WHERE code = ?'
    const { name, identity, age, grade, del, address, sex, code } = req.body;
    try {
        // 更新主表
        await new Promise((resolve, reject) => {
            conn.query(sql, [name, identity, age, grade, del, address, sex, code], (error, results) => {
                if (error) {
                    reject(error);
                } else {
                    resolve(results);
                }
            });
        });

        // 更新其他表的学号字段
        let otherTables = ['login_info', 'grade_query']; // 替换为实际的其他表名
        for (let table of otherTables) {
            await new Promise((resolve, reject) => {
                let updateSql = `UPDATE ${table} SET name = ? , identity = ? , grade = ? WHERE code = ?`;
                conn.query(updateSql, [name, identity, grade, code], (error, results) => {
                    if (error) {
                        reject(error);
                    } else {
                        resolve(results);
                    }
                });
            });
        }

        res.json('true'); // 所有更新成功后发送响应
    } catch (error) {
        console.error(error);
        res.status(500).json('false'); // 发生错误时发送错误响应
    }
})

//教师修改学生信息
app.post('/upDateStudentInfo', passport.authenticate('jwt', { session: false }), async (req, res) => {
    let sql = 'UPDATE person_info SET  name = ?, identity = ?, age = ?, grade = ?, del = ?, address = ?, sex = ? WHERE code = ?'
    const { name, identity, age, grade, del, address, sex, code } = req.body;
    try {
        // 更新主表
        await new Promise((resolve, reject) => {
            conn.query(sql, [name, identity, age, grade, del, address, sex, code], (error, results) => {
                if (error) {
                    reject(error);
                } else {
                    resolve(results);
                }
            });
        });

        // 更新其他表的学号字段
        let otherTables = ['login_info', 'grade_query']; // 替换为实际的其他表名
        for (let table of otherTables) {
            await new Promise((resolve, reject) => {
                let updateSql = `UPDATE ${table} SET name = ?, identity = ?,grade = ? WHERE code = ?`;
                conn.query(updateSql, [name, identity, grade, code], (error, results) => {
                    if (error) {
                        reject(error);
                    } else {
                        resolve(results);
                    }
                });
            });
        }

        res.json('true'); // 所有更新成功后发送响应
    } catch (error) {
        console.error(error);
        res.status(500).json('false'); // 发生错误时发送错误响应
    }
})


//修改成绩
app.post('/upDataStudentGrade', passport.authenticate('jwt', { session: false }), (req, res) => {
    let sql = 'UPDATE grade_query SET math = ?, Python = ?, Web = ?, Java = ?, dataVisual = ? WHERE code = ?'
    const { math, Python, Web, Java, dataVisual, code } = req.body;
    conn.query(sql, [math, Python, Web, Java, dataVisual, code], (error, results) => {
        if (error) {
            res.status(500).json('更新学生成绩失败');
        } else {
            res.status(200).json('更新学生成绩成功');
        }
    })
})

//删除学生成绩
app.post('/deleteStudentGrade', passport.authenticate('jwt', { session: false }), async (req, res) => {
    const { code } = req.body;
    try {
        // 删除 grade_query 表中对应学号的记录
        let deleteSql = 'DELETE FROM grade_query WHERE code = ?';
        await new Promise((resolve, reject) => {
            conn.query(deleteSql, [code], (error, results) => {
                if (error) {
                    reject(error);
                } else {
                    resolve(results);
                }
            });
        });

        let otherTables = ['login_info', 'person_info'];
        for (let table of otherTables) {
            await new Promise((resolve, reject) => {
                let deleteSql = `DELETE FROM ${table} WHERE code = ?`;
                conn.query(deleteSql, [code], (error, results) => {
                    if (error) {
                        reject(error);
                    } else {
                        resolve(results);
                    }
                });
            });
        }

        res.json('删除成功');
    } catch (error) {
        console.error(error);
        res.status(500).json('删除失败');
    }
});

//删除学生信息
app.post('/deleteStudentInfo', passport.authenticate('jwt', { session: false }), async (req, res) => {
    const { code } = req.body;
    try {
        let deleteSql = 'DELETE FROM person_info WHERE code = ?';
        await new Promise((resolve, reject) => {
            conn.query(deleteSql, [code], (error, results) => {
                if (error) {
                    reject(error);
                } else {
                    resolve(results);
                }
            });
        });

        let otherTables = ['login_info', 'grade_query'];
        for (let table of otherTables) {
            await new Promise((resolve, reject) => {
                let deleteSql = `DELETE FROM ${table} WHERE code = ?`;
                conn.query(deleteSql, [code], (error, results) => {
                    if (error) {
                        reject(error);
                    } else {
                        resolve(results);
                    }
                });
            });
        }

        res.json('删除成功');
    } catch (error) {
        console.error(error);
        res.status(500).json('删除失败');
    }
});

//获取学生个人成绩
app.get('/getStudentGrade', passport.authenticate('jwt', { session: false }), (req, res) => {
    let sql = 'SELECT * FROM grade_query WHERE code =' + req.user.code
    conn.query(sql, (error, results) => {
        if (error) res.status(500)
        if (results.length > 0) {
            const Res = results[0]
            const values = Object.values(Res)
            const NewRes1 = values.filter(values => typeof values === 'number')
            const entries = Object.entries(Res)
            const Newres2 = entries.filter(([key, value]) => typeof value === 'number').map(([key]) => key)
            const NewRes = {}
            NewRes.gradeNum = NewRes1
            NewRes.gradeName = Newres2
            res.json(NewRes)
        } else {
            res.json('获取失败')
        }
    })
})

//教师获取学生所有成绩（有参数）
app.get('/getStudentAllGradde', passport.authenticate('jwt', { session: false }), (req, res) => {
    const params = req.query
    const queryParams = params.queryParams
    const queryParamsValue = `%${queryParams}%`
    let sql = 'SELECT * FROM grade_query WHERE code LIKE ? OR name LIKE ? OR grade LIKE ?'
    conn.query(sql, [queryParamsValue, queryParamsValue, queryParamsValue], (error, results) => {
        if (error) res.status(500)
        if (results.length > 0) {
            res.json(results)
        } else {
            res.json('获取失败')
        }
    })
})

//学生获取学生所有成绩（有参数）排名
app.get('/getRankStudentAllGradde', passport.authenticate('jwt', { session: false }), (req, res) => {
    const params = req.query;
    const queryParams = params.queryParams;
    const queryParamsValue = `${queryParams}`;
    let sql = 'SELECT * FROM grade_query WHERE grade = ?';
    conn.query(sql, [queryParamsValue], (error, results) => {
        if (error) {
            return res.status(500).json({ error: '获取失败' });
        }
        if (results.length > 0) {
            res.json(results);
        } else {
            res.json('获取失败');
        }
    });
});

//修改密码
app.post('/changePassword', passport.authenticate('jwt', { session: false }), (req, res) => {
    // let sql = 'UPDATE  login_info SET password = ? WHERE code = ' + req.user.code;
    const { password } = req.body;
    conn.query('UPDATE  login_info SET password = ? WHERE code = ' + req.user.code, [password], (error, results) => {
        if (error) res.status(500);
        if (results.affectedRows > 0) {
            res.json('修改成功')
        } else {
            res.json('修改失败')
        }
    })
})

//获取学生成绩列表
app.get('/getGradeList', passport.authenticate('jwt', { session: false }), (req, res) => {
    let sql = 'SELECT * FROM grade_query WHERE code =' + req.user.code
    conn.query(sql, (error, results) => {
        if (error) res.status(500)
        if (results.length > 0) {
            const Res = results[0]
            const grade = Object.fromEntries(
                Object.entries(Res).filter(([key, value]) => typeof value === 'number')
            )
            const gradeNUm = Object.values(grade)
            const Isgrade = {}
            for (const [key, value] of Object.entries(grade)) {
                Isgrade[key] = value >= 60 ? '通过' : '不通过'
            }
            const newRes = [gradeNUm, Isgrade]
            res.json(newRes)
        }
    })
})

//教师获取所有学生成绩展示页面
app.get('/getStudentAllGradeShow', passport.authenticate('jwt', { session: false }), (req, res) => {
    const params = req.query
    const queryParams = params.queryParams
    const queryParamsValue = `%${queryParams}%`
    let sql = 'SELECT * FROM grade_query WHERE grade LIKE ?'
    conn.query(sql, [queryParamsValue], (error, results) => {
        if (error) { res.status(500); console.log(error) }
        if (results.length > 0) {
            res.json(results)
        } else {
            res.json('获取失败')
        }
    })
})


module.exports = app;